package com.map.station;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {

	public static final String KEY_ROWID = "_id";
	public static final String KEY_STATION = "station";
	public static final String KEY_LAT = "lat";
	public static final String KEY_LNG = "lng";
	public static final String KEY_SERVICE = "service";
	public static final String KEY_DETAIL = "detail";
	public static final String KEY_DESCRIPTION = "description";
	
	private static final String TAG = "DBAdapter";
	
	private static final String DATABASE_NAME = "MyDB";
	private static final String DATABASE_TABLE = "station";
	private static final int DATABASE_VERSION = 2;
	
	private static final String DATABASE_CREATE = 
			"CREATE TABLE station (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"station TEXT NOT NULL, lat TEXT NOT NULL, lng TEXT NOT NULL, " +
			"service TEXT NOT NULL, detail TEXT NOT NULL, description TEXT NOT NULL);";
	
	private final Context context;
	
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;
	
	public DBAdapter(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}
	
	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				// create DB
				db.execSQL(DATABASE_CREATE);
				//db.execSQL("DROP TABLE station");
			} catch(SQLException ex) {
				ex.printStackTrace();
			}
			
			
		}
		
		@Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            //drop table
            db.execSQL("DROP TABLE IF EXISTS station");
            onCreate(db);
        }
    }
	
	//open the database
	public DBAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}
	
	//close the database
	public void close() {
		DBHelper.close();
	}
	
	//insert data into the database
	public long insertContact(String station, String lat, String lng, 
			String service, String detail, String description) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_STATION, station);
		initialValues.put(KEY_LAT, lat);
		initialValues.put(KEY_LNG, lng);
		initialValues.put(KEY_SERVICE, service);
		initialValues.put(KEY_DETAIL, detail);
		initialValues.put(KEY_DESCRIPTION, description);
		
		return db.insert(DATABASE_TABLE, null, initialValues);
	}
	
	//delete data into the database
	public boolean deleteContact(long rowId) {
		return db.delete(DATABASE_TABLE, KEY_ROWID+"="+rowId, null) > 0;
	}
	
	//retrieves all the contact
	public Cursor getAllContacts() {
		return db.query(DATABASE_TABLE, 
				new String[]{KEY_ROWID, KEY_STATION,KEY_LAT, KEY_LNG, KEY_SERVICE,KEY_DESCRIPTION}, 
				null, null, null, null, null);
	}
	
	//retrieve a paticular contact Station
	public Cursor getStation(String rowStation) throws SQLException {
		Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                KEY_STATION, KEY_LAT, KEY_LNG, KEY_SERVICE, KEY_DETAIL, KEY_DESCRIPTION}, KEY_STATION + "=" + "'"+rowStation+"'", null,
                null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
	}
	
	//retrieve a paticular contact Service
	public Cursor getService(String rowService) throws SQLException {
		Cursor mCursor =
				db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
	            KEY_STATION, KEY_LAT, KEY_LNG, KEY_SERVICE, KEY_DETAIL, KEY_DESCRIPTION}, KEY_SERVICE + "=" + "'"+rowService+"'", null,
	            null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
	    return mCursor;
	}
	
	//retrieve a paticular contact Detail
	public Cursor getDetail(String rowDetail) throws SQLException {
		Cursor mCursor =
				db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
	            KEY_STATION, KEY_LAT, KEY_LNG, KEY_SERVICE, KEY_DETAIL, KEY_DESCRIPTION}, KEY_DETAIL + "=" + "'"+rowDetail+"'", null,
	            null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
	    return mCursor;
	}
		
	//SPECIAL get contact
	public Cursor getSpecial(String rowLng) throws SQLException {
		Cursor mCursor =
				db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
				KEY_STATION, KEY_LAT, KEY_LNG, KEY_SERVICE, KEY_DETAIL, KEY_DESCRIPTION}, KEY_LNG + "=" + "'" +rowLng+ "'", null, 
				null, null, null, null);
		if(mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	//updates a contact
    public boolean updateContact(long rowId, String station, String lat, String lng, String service, String detail, String description) {
    	ContentValues args = new ContentValues();
        args.put(KEY_STATION, station);
        args.put(KEY_LAT, lat);
        args.put(KEY_LNG, lng);
        args.put(KEY_SERVICE, service);
        args.put(KEY_DETAIL, detail);
        args.put(KEY_DESCRIPTION, description);
        
        return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}
